import os
import xlrd
import xlwt

# 获取当前目录下的所有.xls文件
files = [f for f in os.listdir('.') if os.path.isfile(f) and f.endswith('.xls')]

paths = "./转换之后/"
# 检查路径是否存在，如果不存在则创建
if not os.path.exists(paths):
    os.makedirs(paths)

for index, file_path in enumerate(files):
    try:
        workbook = xlrd.open_workbook(file_path)
    except xlrd.biffh.XLRDError:
        print(f"无法打开文件: {file_path}")
        continue

    first_sheet = workbook.sheet_by_index(0)

    data = []
    for row in range(first_sheet.nrows):
        row_values = first_sheet.row_values(row)
        if isinstance(row_values[0], (int, float)):  # 序号列
            data.append([
                row_values[1],  # ZKW 零件号
                row_values[2],  # 零件描述
                row_values[3],  # 批号
                row_values[4],  # 订单号
                row_values[7]   # 交付数量
            ])
            continue

        new_workbook = xlwt.Workbook()
        new_worksheet = new_workbook.add_sheet('供应商供货清单')

        info = [
            ('发运编号：', '', '', '', '', '', '客户：', '长春盖尔瑞孚艾斯曼汽车零部件有限公司', '', '', ''),
            ('供应商', '', '', '', '', '', '采购订单编号：', '', '', '', ''),
            ('供应商地址：', '', '', '', '', '', '收货地址：', '', '', '', ''),
            ('序号', '零件号', '零件名称', '到货数量', '单位', '使用车型', '到货包装数', '标准包装数', '批次', '送货通知单号', '备注')
        ]

        font_style = xlwt.easyxf('font: height 280, bold on;')  # 设置字体大小为14，加粗
        borders = xlwt.Borders()  # 创建边框对象
        borders.left = xlwt.Borders.THIN
        borders.right = xlwt.Borders.THIN
        borders.top = xlwt.Borders.THIN
        borders.bottom = xlwt.Borders.THIN
        font_style.borders = borders  # 将边框应用到字体样式

        for row, row_data in enumerate(info):
            for col, cell_data in enumerate(row_data):
                new_worksheet.write(row + 1, col, cell_data, font_style)

        for col in range(11):  # 设置所有列的宽度为15个字符宽度
            new_worksheet.col(col).width = 256 * 23

        title_style = xlwt.easyxf('align: horiz center, vert center; font: height 280, bold on;')
        title_style.borders = borders
        new_worksheet.write_merge(0, 0, 0, 10, '供应商供货清单（国内供应商）', title_style)

        data_style = xlwt.easyxf('font: height 280;')
        data_style.borders = borders

        for index, (part_number, part_name, batch, delivery_notice, arrival_quantity) in enumerate(data):
            new_worksheet.write(index + 5, 0, index + 1, data_style)
            new_worksheet.write(index + 5, 1, part_number, data_style)
            new_worksheet.write(index + 5, 2, part_name, data_style)
            new_worksheet.write(index + 5, 3, arrival_quantity, data_style)
            new_worksheet.write(index + 5, 4, '', data_style)  # 单位
            new_worksheet.write(index + 5, 5, '', data_style)  # 使用车型
            new_worksheet.write(index + 5, 6, '', data_style)  # 到货包装数
            new_worksheet.write(index + 5, 7, '', data_style)  # 标准包装数
            new_worksheet.write(index + 5, 8, batch, data_style)  # 批次
            new_worksheet.write(index + 5, 9, delivery_notice, data_style)
            new_worksheet.write(index + 5, 10, '', data_style)  # 备注

        new_file_path = os.path.join(paths, f"{index}_{file_path}")
        new_workbook.save(new_file_path)
